package com.xbniao.gcfs.common.util.ExportExcel;

import com.xbniao.gcfs.common.enums.FeePayStatusEnum;
import com.xbniao.gcfs.common.response.model.FeePayInfo;
import com.xbniao.gcfs.common.util.DateUtil;
import com.xbniao.gcfs.dto.GcfsEbayCustomOrderProduct;
import com.xbniao.gcfs.dto.GcfsFeePayDetail;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExportFeePay {

  public static HSSFWorkbook createFeePayExcel(List<FeePayInfo> pays) {
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {

      HSSFSheet sheet = workbook.createSheet("应收明细");
      sheet.setDisplayGridlines(false);
      sheet.autoSizeColumn(1);
      // 设置单元格样式
      HSSFCellStyle style = setCellStyle(workbook, 1);
      HSSFCellStyle style1 = setCellStyle(workbook, 2);
      List<String> payHead = createPayHead("2");
      int row = 0;
      HSSFRow h_row = sheet.createRow(row);
      setHead(payHead, h_row, style);
      if (pays != null) {
        for (FeePayInfo info : pays) {
          HSSFCell cell = null;
          int len = 0;
          if (!CollectionUtils.isEmpty(info.getPayDetails())) {
            len = info.getPayDetails().size();
          }
          if (len > 1) {
            row = row + 1;
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(row, row + len - 1, 0, 0);
            CellRangeAddress cellRangeAddress2 = new CellRangeAddress(row, row + len - 1, 1, 1);
            CellRangeAddress cellRangeAddress3 = new CellRangeAddress(row, row + len - 1, 2, 2);
            CellRangeAddress cellRangeAddress4 = new CellRangeAddress(row, row + len - 1, 3, 3);
            CellRangeAddress cellRangeAddress5 = new CellRangeAddress(row, row + len - 1, 4, 4);
            sheet.addMergedRegion(cellRangeAddress1);
            sheet.addMergedRegion(cellRangeAddress2);
            sheet.addMergedRegion(cellRangeAddress3);
            sheet.addMergedRegion(cellRangeAddress4);
            sheet.addMergedRegion(cellRangeAddress5);
            h_row = sheet.createRow(row);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());
            cell = h_row.createCell(1);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getAmount() == null ? 0 : info.getAmount());
            cell = h_row.createCell(2);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
            cell = h_row.createCell(3);
            cell.setCellStyle(style1);
            cell.setCellValue(FeePayStatusEnum.getNameByCode(info.getStatus()));
            cell = h_row.createCell(4);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCloseTime(), "yyyy-MM-dd HH:mm:ss"));
            int flag = row;
            row = row - 1;
            for (int i = 0; i < len; i++) {
              row = row + 1;
              if (flag != row) {
                h_row = sheet.createRow(row);
              }
              h_row.setHeight((short) 500);
              GcfsFeePayDetail detail = info.getPayDetails().get(i);
              cell = h_row.createCell(5);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getFeeItem());
              cell = h_row.createCell(6);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getAmount());
              cell = h_row.createCell(7);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getBillNo());
              cell = h_row.createCell(8);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCustomOrderNo());
              /*cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getSupplyName());*/
              cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue(DateUtil.dateToStr(detail.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
              cell = h_row.createCell(10);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCreateUser());
              cell = h_row.createCell(11);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getNote());
            }
            setBorder(cellRangeAddress1, sheet, workbook);
            setBorder(cellRangeAddress2, sheet, workbook);
            setBorder(cellRangeAddress3, sheet, workbook);
            setBorder(cellRangeAddress4, sheet, workbook);
            setBorder(cellRangeAddress5, sheet, workbook);
          } else {
            row = row + 1;
            h_row = sheet.createRow(row);
            h_row.setHeight((short) 500);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());
            cell = h_row.createCell(1);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getAmount() == null ? 0 : info.getAmount());
            cell = h_row.createCell(2);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
            cell = h_row.createCell(3);
            cell.setCellStyle(style1);
            cell.setCellValue(FeePayStatusEnum.getNameByCode(info.getStatus()));
            cell = h_row.createCell(4);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCloseTime(), "yyyy-MM-dd HH:mm:ss"));
            if (!CollectionUtils.isEmpty(info.getPayDetails())) {
              GcfsFeePayDetail detail = info.getPayDetails().get(0);
              cell = h_row.createCell(5);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getFeeItem());
              cell = h_row.createCell(6);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getAmount());
              cell = h_row.createCell(7);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getBillNo());
              cell = h_row.createCell(8);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCustomOrderNo());
              /*cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getSupplyName());*/
              cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue(DateUtil.dateToStr(detail.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
              cell = h_row.createCell(10);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCreateUser());
              cell = h_row.createCell(11);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getNote());
            } else {
              cell = h_row.createCell(5);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(6);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(7);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(8);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(10);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(11);
              cell.setCellStyle(style1);
              cell.setCellValue("");
            }

          }
        }
        for (int i = 0; i < payHead.size(); i++) {
          sheet.autoSizeColumn(i);
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    List<FeePayInfo> pays1 = new ArrayList<>();

    for (FeePayInfo feePayInfo : pays) {
      List<GcfsFeePayDetail> payDetails = feePayInfo.getPayDetails();
      if (null != payDetails && !payDetails.isEmpty()) {
        Iterator<GcfsFeePayDetail> it = payDetails.iterator();
        while (it.hasNext()) {
          GcfsFeePayDetail x = it.next();
          String feeItem = x.getFeeItem();
          if (!"法检费".equals(feeItem)) {
            it.remove();
          }
        }
        if (!payDetails.isEmpty()) {
          pays1.add(feePayInfo);

        }
      }
    }
    if (!pays1.isEmpty()) {
      createLegalInspectionExcel(pays1, workbook);
    }
    return workbook;
  }

  public static HSSFWorkbook createPayableExcel(List<FeePayInfo> pays) {
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {
      HSSFSheet sheet = workbook.createSheet("应付明细");
      sheet.setDisplayGridlines(false);
      sheet.autoSizeColumn(1);
      // 设置单元格样式
      HSSFCellStyle style = setCellStyle(workbook, 1);
      HSSFCellStyle style1 = setCellStyle(workbook, 2);
      List<String> payHead = createPayHead("1");
      int row = 0;
      HSSFRow h_row = sheet.createRow(row);
      setHead(payHead, h_row, style);
      if (pays != null) {
        for (FeePayInfo info : pays) {
          HSSFCell cell = null;
          int len = 0;
          if (!CollectionUtils.isEmpty(info.getPayDetails())) {
            len = info.getPayDetails().size();
          }
          if (len > 1) {
            row = row + 1;
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(row, row + len - 1, 0, 0);
            CellRangeAddress cellRangeAddress2 = new CellRangeAddress(row, row + len - 1, 1, 1);
            CellRangeAddress cellRangeAddress3 = new CellRangeAddress(row, row + len - 1, 2, 2);
            CellRangeAddress cellRangeAddress4 = new CellRangeAddress(row, row + len - 1, 3, 3);
            CellRangeAddress cellRangeAddress5 = new CellRangeAddress(row, row + len - 1, 4, 4);
            sheet.addMergedRegion(cellRangeAddress1);
            sheet.addMergedRegion(cellRangeAddress2);
            sheet.addMergedRegion(cellRangeAddress3);
            sheet.addMergedRegion(cellRangeAddress4);
            sheet.addMergedRegion(cellRangeAddress5);
            h_row = sheet.createRow(row);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());
            cell = h_row.createCell(1);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getAmount() == null ? 0 : info.getAmount());
            cell = h_row.createCell(2);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
            cell = h_row.createCell(3);
            cell.setCellStyle(style1);
            cell.setCellValue(FeePayStatusEnum.getNameByCode(info.getStatus()));
            cell = h_row.createCell(4);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCloseTime(), "yyyy-MM-dd HH:mm:ss"));
            int flag = row;
            row = row - 1;
            for (int i = 0; i < len; i++) {
              row = row + 1;
              if (flag != row) {
                h_row = sheet.createRow(row);
              }
              h_row.setHeight((short) 500);
              GcfsFeePayDetail detail = info.getPayDetails().get(i);
              cell = h_row.createCell(5);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getFeeItem());
              cell = h_row.createCell(6);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getAmount());
              cell = h_row.createCell(7);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getBillNo());
              cell = h_row.createCell(8);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCustomOrderNo());
              cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getSupplyName());
              cell = h_row.createCell(10);
              cell.setCellStyle(style1);
              cell.setCellValue(DateUtil.dateToStr(detail.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
              cell = h_row.createCell(11);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCreateUser());
              cell = h_row.createCell(12);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getNote());
            }
            setBorder(cellRangeAddress1, sheet, workbook);
            setBorder(cellRangeAddress2, sheet, workbook);
            setBorder(cellRangeAddress3, sheet, workbook);
            setBorder(cellRangeAddress4, sheet, workbook);
            setBorder(cellRangeAddress5, sheet, workbook);
          } else {
            row = row + 1;
            h_row = sheet.createRow(row);
            h_row.setHeight((short) 500);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());
            cell = h_row.createCell(1);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getAmount() == null ? 0 : info.getAmount());
            cell = h_row.createCell(2);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
            cell = h_row.createCell(3);
            cell.setCellStyle(style1);
            cell.setCellValue(FeePayStatusEnum.getNameByCode(info.getStatus()));
            cell = h_row.createCell(4);
            cell.setCellStyle(style1);
            cell.setCellValue(DateUtil.dateToStr(info.getCloseTime(), "yyyy-MM-dd HH:mm:ss"));
            if (!CollectionUtils.isEmpty(info.getPayDetails())) {
              GcfsFeePayDetail detail = info.getPayDetails().get(0);
              cell = h_row.createCell(5);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getFeeItem());
              cell = h_row.createCell(6);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getAmount());
              cell = h_row.createCell(7);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getBillNo());
              cell = h_row.createCell(8);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCustomOrderNo());
              cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getSupplyName());
              cell = h_row.createCell(10);
              cell.setCellStyle(style1);
              cell.setCellValue(DateUtil.dateToStr(detail.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
              cell = h_row.createCell(11);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCreateUser());
              cell = h_row.createCell(12);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getNote());
            } else {
              cell = h_row.createCell(5);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(6);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(7);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(8);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(9);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(10);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(11);
              cell.setCellStyle(style1);
              cell.setCellValue("");
              cell = h_row.createCell(12);
              cell.setCellStyle(style1);
              cell.setCellValue("");
            }

          }
        }
        for (int i = 0; i < payHead.size(); i++) {
          sheet.autoSizeColumn(i);
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return workbook;
  }

  public static HSSFWorkbook createLegalInspectionExcel(List<FeePayInfo> pays, HSSFWorkbook workbook) {
    try {
      HSSFSheet sheet = workbook.createSheet("法检费明细");
      sheet.setDisplayGridlines(false);
      sheet.autoSizeColumn(1);
      // 设置单元格样式
      HSSFCellStyle style = setCellStyle(workbook, 1);
      HSSFCellStyle style1 = setCellStyle(workbook, 2);
      List<String> payHead = createPayHead("3");
      int row = 0;
      HSSFRow h_row = sheet.createRow(row);
      setHead(payHead, h_row, style);
      if (pays != null) {
        for (FeePayInfo info : pays) {
          HSSFCell cell = null;
          int len = 0;
          if (!CollectionUtils.isEmpty(info.getPayDetails())) {
            len = info.getPayDetails().size();
          } else {
            row = row + 1;
            h_row = sheet.createRow(row);
            h_row.setHeight((short) 500);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());
            cell = h_row.createCell(1);
            cell.setCellStyle(style1);
            cell.setCellValue("");
            cell = h_row.createCell(2);
            cell.setCellStyle(style1);
            cell.setCellValue("");
            cell = h_row.createCell(3);
            cell.setCellStyle(style1);
            cell.setCellValue("");
            cell = h_row.createCell(4);
            cell.setCellStyle(style1);
            cell.setCellValue("");
            cell = h_row.createCell(5);
            cell.setCellStyle(style1);
            cell.setCellValue("");
            cell = h_row.createCell(6);
            cell.setCellStyle(style1);
            cell.setCellValue("");
            continue;
          }
          if (len > 1) {
            row = row + 1;
            int mergeCount = getMergeCount(pays);
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(row, row + mergeCount - 1, 0, 0);
            sheet.addMergedRegion(cellRangeAddress1);
            h_row = sheet.createRow(row);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());
            int flag = row;
            row = row - 1;
            for (int i = 0; i < len; i++) {
              int len1 = 0;
              row = row + 1;
              if (flag != row) {
                h_row = sheet.createRow(row);
              }
              GcfsFeePayDetail detail = info.getPayDetails().get(i);
              if (!CollectionUtils.isEmpty(detail.getCustomOrderProducts())) {
                len1 = detail.getCustomOrderProducts().size();
              }
              if (len1 > 1) {
                CellRangeAddress cellRangeAddress2 = new CellRangeAddress(row, row + len1 - 1, 1, 1);
                CellRangeAddress cellRangeAddress3 = new CellRangeAddress(row, row + len1 - 1, 2, 2);
                CellRangeAddress cellRangeAddress4 = new CellRangeAddress(row, row + len1 - 1, 3, 3);
                sheet.addMergedRegion(cellRangeAddress2);
                sheet.addMergedRegion(cellRangeAddress3);
                sheet.addMergedRegion(cellRangeAddress4);
                cell = h_row.createCell(1);
                cell.setCellStyle(style1);
                cell.setCellValue(detail.getFeeItem());
                cell = h_row.createCell(2);
                cell.setCellStyle(style1);
                cell.setCellValue(detail.getAmount());
                cell = h_row.createCell(3);
                cell.setCellStyle(style1);
                cell.setCellValue(detail.getCustomOrderNo());
                int flag1 = row;
                row = row - 1;
                List<GcfsEbayCustomOrderProduct> customOrderProducts = detail.getCustomOrderProducts();
                for (int j = 0; j < len1; j++) {
                  row = row + 1;
                  if (flag1 != row) {
                    h_row = sheet.createRow(row);
                  }
                  GcfsEbayCustomOrderProduct gcfsEbayCustomOrderProduct = customOrderProducts.get(j);
                  h_row.setHeight((short) 500);
                  cell = h_row.createCell(4);
                  cell.setCellStyle(style1);
                  cell.setCellValue(gcfsEbayCustomOrderProduct.getUserName());
                  cell = h_row.createCell(5);
                  cell.setCellStyle(style1);
                  cell.setCellValue(gcfsEbayCustomOrderProduct.getMerchantSku());
                  cell = h_row.createCell(6);
                  cell.setCellStyle(style1);
                  cell.setCellValue(gcfsEbayCustomOrderProduct.getCfOrderCode());
                }
                setBorder(cellRangeAddress2, sheet, workbook);
                setBorder(cellRangeAddress3, sheet, workbook);
                setBorder(cellRangeAddress4, sheet, workbook);
              } else {
                cell = h_row.createCell(1);
                cell.setCellStyle(style1);
                cell.setCellValue(detail.getFeeItem());
                cell = h_row.createCell(2);
                cell.setCellStyle(style1);
                cell.setCellValue(detail.getAmount());
                cell = h_row.createCell(3);
                cell.setCellStyle(style1);
                cell.setCellValue(detail.getCustomOrderNo());
                if (!CollectionUtils.isEmpty(detail.getCustomOrderProducts())) {
                  GcfsEbayCustomOrderProduct gcfsEbayCustomOrderProduct = detail.getCustomOrderProducts().get(0);
                  h_row.setHeight((short) 500);
                  cell = h_row.createCell(4);
                  cell.setCellStyle(style1);
                  cell.setCellValue(gcfsEbayCustomOrderProduct.getUserName());
                  cell = h_row.createCell(5);
                  cell.setCellStyle(style1);
                  cell.setCellValue(gcfsEbayCustomOrderProduct.getMerchantSku());
                  cell = h_row.createCell(6);
                  cell.setCellStyle(style1);
                  cell.setCellValue(gcfsEbayCustomOrderProduct.getCfOrderCode());
                } else {
                  h_row.setHeight((short) 500);
                  cell = h_row.createCell(4);
                  cell.setCellStyle(style1);
                  cell.setCellValue("");
                  cell = h_row.createCell(5);
                  cell.setCellStyle(style1);
                  cell.setCellValue("");
                  cell = h_row.createCell(6);
                  cell.setCellStyle(style1);
                  cell.setCellValue("");
                }
              }
            }
            setBorder(cellRangeAddress1, sheet, workbook);
          } else if (len == 1) {
            row = row + 1;
            h_row = sheet.createRow(row);
            cell = h_row.createCell(0);
            cell.setCellStyle(style1);
            cell.setCellValue(info.getTrackNo());

            int len1 = 0;
            if (!CollectionUtils.isEmpty(info.getPayDetails().get(0).getCustomOrderProducts())) {
              len1 = info.getPayDetails().get(0).getCustomOrderProducts().size();
            }
            GcfsFeePayDetail detail = info.getPayDetails().get(0);
            if (len1 > 1) {
              CellRangeAddress cellRangeAddress2 = new CellRangeAddress(row, row + len1 - 1, 1, 1);
              CellRangeAddress cellRangeAddress3 = new CellRangeAddress(row, row + len1 - 1, 2, 2);
              CellRangeAddress cellRangeAddress4 = new CellRangeAddress(row, row + len1 - 1, 3, 3);
              sheet.addMergedRegion(cellRangeAddress2);
              sheet.addMergedRegion(cellRangeAddress3);
              sheet.addMergedRegion(cellRangeAddress4);
              cell = h_row.createCell(1);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getFeeItem());
              cell = h_row.createCell(2);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getAmount());
              cell = h_row.createCell(3);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCustomOrderNo());
              int flag1 = row;
              row = row - 1;
              List<GcfsEbayCustomOrderProduct> customOrderProducts = detail.getCustomOrderProducts();
              for (int j = 0; j < len1; j++) {
                row = row + 1;
                if (flag1 != row) {
                  h_row = sheet.createRow(row);
                }
                GcfsEbayCustomOrderProduct gcfsEbayCustomOrderProduct = customOrderProducts.get(j);
                h_row.setHeight((short) 500);
                cell = h_row.createCell(4);
                cell.setCellStyle(style1);
                cell.setCellValue(gcfsEbayCustomOrderProduct.getUserName());
                cell = h_row.createCell(5);
                cell.setCellStyle(style1);
                cell.setCellValue(gcfsEbayCustomOrderProduct.getMerchantSku());
                cell = h_row.createCell(6);
                cell.setCellStyle(style1);
                cell.setCellValue(gcfsEbayCustomOrderProduct.getCfOrderCode());
              }
              setBorder(cellRangeAddress2, sheet, workbook);
              setBorder(cellRangeAddress3, sheet, workbook);
              setBorder(cellRangeAddress4, sheet, workbook);
            } else {
              cell = h_row.createCell(1);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getFeeItem());
              cell = h_row.createCell(2);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getAmount());
              cell = h_row.createCell(3);
              cell.setCellStyle(style1);
              cell.setCellValue(detail.getCustomOrderNo());
              if (!CollectionUtils.isEmpty(info.getPayDetails().get(0).getCustomOrderProducts())) {
                GcfsEbayCustomOrderProduct gcfsEbayCustomOrderProduct = info.getPayDetails().get(0)
                    .getCustomOrderProducts().get(0);
                h_row.setHeight((short) 500);
                cell = h_row.createCell(4);
                cell.setCellStyle(style1);
                cell.setCellValue(gcfsEbayCustomOrderProduct.getUserName());
                cell = h_row.createCell(5);
                cell.setCellStyle(style1);
                cell.setCellValue(gcfsEbayCustomOrderProduct.getMerchantSku());
                cell = h_row.createCell(6);
                cell.setCellStyle(style1);
                cell.setCellValue(gcfsEbayCustomOrderProduct.getCfOrderCode());
              } else {
                h_row.setHeight((short) 500);
                cell = h_row.createCell(4);
                cell.setCellStyle(style1);
                cell.setCellValue("");
                cell = h_row.createCell(5);
                cell.setCellStyle(style1);
                cell.setCellValue("");
                cell = h_row.createCell(6);
                cell.setCellStyle(style1);
                cell.setCellValue("");
              }
            }

          }
        }
      }
      for (int i = 0; i < payHead.size(); i++) {
        sheet.autoSizeColumn(i);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return workbook;

  }

  private static int getMergeCount(List<FeePayInfo> pays) {
    int mergeCounts = 0;
    if (null != pays && !pays.isEmpty()) {
      for (FeePayInfo feePayInfo : pays) {
        List<GcfsFeePayDetail> payDetails = feePayInfo.getPayDetails();
        if (!CollectionUtils.isEmpty(payDetails)) {
          int payDetailsSize = payDetails.size();
          int gcfsFeePayDetailSize = 0;
          for (GcfsFeePayDetail gcfsFeePayDetail : payDetails) {
            List<GcfsEbayCustomOrderProduct> customOrderProducts = gcfsFeePayDetail.getCustomOrderProducts();
            if (!CollectionUtils.isEmpty(customOrderProducts)) {
              gcfsFeePayDetailSize += customOrderProducts.size();
            }
          }
          return gcfsFeePayDetailSize > payDetailsSize ? gcfsFeePayDetailSize : payDetailsSize;
        }
      }
    }
    return mergeCounts;
  }

  private static void setBorder(CellRangeAddress cellRangeAddress, Sheet sheet, Workbook wb) throws Exception {
    RegionUtil.setBorderLeft(1, cellRangeAddress, sheet, wb);
    RegionUtil.setBorderBottom(1, cellRangeAddress, sheet, wb);
    RegionUtil.setBorderRight(1, cellRangeAddress, sheet, wb);
    RegionUtil.setBorderTop(1, cellRangeAddress, sheet, wb);

  }

  private static void setHead(List<String> payHead, HSSFRow h_row, HSSFCellStyle style) {

    h_row.setHeight((short) 500);
    for (int c = 0; c < payHead.size(); c++) {
      HSSFCell cell = h_row.createCell(c);
      cell.setCellValue(payHead.get(c));
      cell.setCellStyle(style);
    }
  }

  private static List<String> createPayHead(String type) {
    List<String> head = new ArrayList<>();
    switch (type) {
    case "1": {
      head.add("主提运单号");
      head.add("应付总金额");
      head.add("记账时间");
      head.add("关账状态");
      head.add("关账时间");
      head.add("费用项名称");
      head.add("应付金额");
      head.add("关联单据");
      head.add("关联报关单");
      head.add("供应商");
      head.add("发生时间");
      head.add("记录人");
      head.add("备注");
    }
      break;
    case "2": {
      head.add("主提运单号");
      head.add("应收总金额");
      head.add("记账时间");
      head.add("关账状态");
      head.add("关账时间");
      head.add("费用项名称");
      head.add("应收金额");
      head.add("关联单据");
      head.add("关联报关单");
      head.add("发生时间");
      head.add("记录人");
      head.add("备注");
    }
      break;

    case "3": {
      head.add("主提运单号");
      head.add("费用项名称");
      head.add("应收金额");
      head.add("关联报关单");
      head.add("用户名");
      head.add("商家SUK");
      head.add("CF订单号");
    }
      break;
    default:
      head.add(null);
      break;
    }

    return head;
  }

  public static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int b) {
    HSSFCellStyle style = workbook.createCellStyle();
    // 设置cell样式
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    // 创建字体样式
    HSSFFont font = workbook.createFont();
    font.setFontName("微软雅黑");
    if (b == 1) {
      font.setFontHeightInPoints((short) 11);
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    } else {
      font.setFontHeightInPoints((short) 10);
    }
    style.setFont(font);
    return style;
  }
}
